package sef.impl.repository;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import javax.sql.DataSource;

import org.apache.log4j.Logger;
import org.springframework.stereotype.Repository;

import sef.domain.Employee;
import sef.domain.EmployeeDetail;
import sef.exceptions.EmployeeRetrieveException;
import sef.interfaces.repository.EmployeeRepository;
import sef.interfaces.repository.ProjectRepository;
import sef.interfaces.repository.SkillRepository;

@Repository
public class StubEmployeeRepositoryImpl implements EmployeeRepository {


	//DataSource class encapsulates the driver, database url, username and 
	//password information.  The dataSource object is automatically created by 
	//the Spring framework and passed to the constructor therefore there's no need 
	//to instantiate the dataSource variable. A connection can be acquired by 
	//accessing the getConnection method of dataSource. 
	//
	//Tip: create member variables in this class that will contain the objects 
	//passed by the Spring framework so that other methods can access the objects.

	private static Logger log = Logger.getLogger(StubEmployeeRepositoryImpl.class);
	
	private DataSource dataSource;
	
	/*@PersistenceContext
	private EntityManager em; */

	ProjectRepository spri;
	SkillRepository ssri;

	
	public StubEmployeeRepositoryImpl(DataSource dataSource, ProjectRepository projectDAO, SkillRepository skillDAO) {
		this.dataSource = dataSource;
		this.spri = projectDAO;
		this.ssri = skillDAO;
	}

	@Override
	public List<Employee> findEmployeesByName(String firstName, String lastName) throws EmployeeRetrieveException {
		
		log.info("Looking for employee at storage. (Using findEmployeesByName(FN, LM) )");
		
		String sql = "SELECT * FROM employee WHERE fisrtName LIKE ? AND lastName LIKE ?;";
		List<Employee> list = new ArrayList<Employee>();
		 try {
			
			Connection conn = dataSource.getConnection();
			
			PreparedStatement ps = conn.prepareStatement(sql);
			
			ps.setString(1, "%" + firstName + "%");
			ps.setString(2, "%" + lastName + "%");
			
			ResultSet rs = ps.executeQuery();
			
			if(!rs.next()) 
				throw new EmployeeRetrieveException("No such employee found with this name", new Throwable());
			else
				rs.beforeFirst();
			
			while (rs.next()) {
				list.add(new Employee(rs.getLong(1), rs.getString(2), rs.getString(3), rs.getString(4), rs.getString(5), rs.getString(6), rs.getString(7)));
			}
			
			conn.close();
			
		} catch (SQLException e) {
			log.error(e.getMessage());
			e.printStackTrace();
		} 
		
		return list;
	}

	@Override
	public Employee findEmployeeByID(long employeeID) {

		log.info("Looking for employee at storage. (Using findEmployeeByID(employeeID) )");
		
		String sql = "SELECT * FROM Employee WHERE ID = ?;";
		try {
			
			Connection conn = dataSource.getConnection();
			PreparedStatement ps = conn.prepareStatement(sql);
			
			ps.setLong(1, employeeID);
			
			
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				return new Employee(rs.getLong(0), rs.getString(1), rs.getString(2), rs.getString(3), rs.getString(4), rs.getString(5), rs.getString(6));
			}
			
			conn.close();
			
		} catch (SQLException e) {
			log.error(e.getMessage());
			e.printStackTrace();
		}
		
		return null;
	}

	@Override
	public List<Employee> findEmployeesByProject(long projectID) throws EmployeeRetrieveException {

		log.info("Looking for employee at storage. (Using findEmployeesByProject(projectID) )");
		
		List<Employee> list = new ArrayList<Employee>();
		try {
			Connection conn = dataSource.getConnection();
			
			String sql  = "SELECT e . * FROM (employeeprojectdetail AS epd "
					+ "INNER JOIN employeedetail AS ed ON epd.employeeDetail_ID = ed.ID ) "
					+ "INNER JOIN employee AS e ON ed.employee_ID = e.ID "
					+ "WHERE epd.project_ID =?";
		
			log.info(sql);
			PreparedStatement ps = conn.prepareStatement(sql);
			ps.setLong(1, projectID);
			
			ResultSet rs = ps.executeQuery();

			if(!rs.next())
				throw new EmployeeRetrieveException("No employee attached to this project!", new Throwable());
			else
				rs.beforeFirst();
				
			while(rs.next())
				list.add(new Employee(rs.getLong(1), rs.getString(2), rs.getString(3), rs.getString(4), rs.getString(5), rs.getString(6), rs.getString(7)));
			
			conn.close();
		} catch (SQLException e) {
			log.error(e.getMessage());
			e.printStackTrace();
		}
		
		return list;
	}

	@Override
	public EmployeeDetail getEmployeeDetails(long employeeID) {
		
			log.info("Looking for employee at storage. (Using findEmployeesByProject(projectID) )");
		
			Employee e = null;
			EmployeeDetail ed = new EmployeeDetail();
			
		try {
			Connection conn = dataSource.getConnection();
			String sql = "SELECT * FROM employee WHERE ID = ?";
			PreparedStatement ps = conn.prepareStatement(sql);
			ps.setLong(1, employeeID);
			
			ResultSet rs = ps.executeQuery();
			log.info(rs.toString());
			while(rs.next()) {
				e = new Employee(employeeID, rs.getString(1), rs.getString(2), rs.getString(3), rs.getString(4), rs.getString(5), rs.getString(6));
			}
			
			//TODO Continuar
			ed.setSkillList(ssri.findEmployeeSkills(employeeID));
			//TODO Continuar
			ed.setProjectList(spri.getEmployeeProjectHistory(employeeID));	
	
			ed.setEmployee(e);
			
			conn.close();
		} catch (SQLException esql) {
			// TODO Auto-generated catch block
			esql.printStackTrace();
		}
		
		return ed;
	}


}